﻿/******************************************************
* author :  cwj
* email  :  chenwenji_360@live.com 
* history:  created by cwj 2015/8/17 2:49:32 
* clrversion :4.0.30319.42000
******************************************************/

using Machine.DataAccess.Linq;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;

namespace Machine.DataAccess.Common.ORM
{
    class SqlServerSchemInfo : DbSchemInfoBase
    {
        public SqlServerSchemInfo(ProviderElement providerElement) : base(providerElement) { }

        private static readonly string GET_PRIMARY_KEY = @"SELECT c.NAME AS [CloumName] FROM sys.columns c LEFT OUTER JOIN (sys.index_columns ic INNER JOIN sys.indexes i ON ic.object_id = i.object_id	AND i.is_primary_key = 1) ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE c.object_id = OBJECT_ID('{0}') AND i.is_primary_key IS NOT NULL";
        private static Dictionary<string, string> primaryKeyCache = new Dictionary<string, string>();

        public override string GetPrimaryKey(string tableName)
        {
            if (!primaryKeyCache.ContainsKey(tableName))
            {
                var sql = string.Format(GET_PRIMARY_KEY, tableName);
                var colums = new DataReader<PrimaryKeyTemp>(new TranslateResult(sql, new List<DbParameter>()), this.ProviderElement,null).ToList();
                if (colums.Count == 0) return null;
                foreach (var item in colums)
                {
                    item.TableName = tableName;
                }
                primaryKeyCache[tableName] = colums.First(c => c.TableName.ToLower() == tableName).CloumName.ToLower();
            }
            return primaryKeyCache[tableName];
        }

        public override bool GetPrimaryKeyType(string tableName)
        {
            return false;
        }

        //private static readonly string GET_FK = "SELECT t.REFERENCED_TABLE_NAME AS 'table', t.COLUMN_NAME AS 'from', t.REFERENCED_COLUMN_NAME AS 'to' FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS t WHERE t.TABLE_NAME = '{0}' AND t.TABLE_SCHEMA = '{1}' AND t.CONSTRAINT_NAME <> 'primary'";
        private static readonly string GET_FK = "SELECT	object_name(B.rkeyid) AS [table], col_name(A.parent_obj, B.fkey) AS [from],	col_name(B.rkeyid, B.rkey) AS [to] FROM	sysobjects AS A JOIN sysforeignkeys B ON A.id = B.constid WHERE	object_name(A.parent_obj) = '{0}'";
        private static Dictionary<string, Dictionary<string, DbForeignKeyInfo[]>> fkCache = new Dictionary<string, Dictionary<string, DbForeignKeyInfo[]>>();
       
        public override DbForeignKeyInfo[] GetForeignKey(string tableName)
        {
            tableName = tableName.ToLower();
            if (!fkCache.ContainsKey(ProviderElement.Key)) fkCache[ProviderElement.Key] = new Dictionary<string, DbForeignKeyInfo[]>();
            if (!fkCache[ProviderElement.Key].ContainsKey(tableName) || fkCache[ProviderElement.Key][tableName] == null)
            {
                var sql = string.Format(GET_FK, tableName, ProviderElement.Key);
                fkCache[ProviderElement.Key][tableName] = new DataReader<DbForeignKeyInfo>(new TranslateResult(sql, new DbParameter[0]), this.ProviderElement, null).ToArray();
            }
            return fkCache[ProviderElement.Key][tableName];
        }

        private static readonly string GET_ALLTABLE = "SELECT name FROM sysobjects WHERE type='U'";
        public override string[] GetAllTables()
        {
            return new DataReader<string>(new TranslateResult(GET_ALLTABLE, new DbParameter[0]), this.ProviderElement, null).ToArray();
        }

        public override bool ExtistTable(string name)
        {
            return new DataReader<string>(new TranslateResult(GET_ALLTABLE, new DbParameter[0]), this.ProviderElement, null).FirstOrDefault(t => t == name) != null;
        }

        public override Tuple<string, DbForeignKeyInfo[]> GetOuterFKey(Type bigTypeName, Type collectionTypeName)
        {
            var pkTableName = bigTypeName.GetTableName();
            var fkTableName = collectionTypeName.GetTableName();

            var outerFk = this.GetForeignKey(fkTableName).ToDictionary(x => x.Table);
            if (outerFk.ContainsKey(pkTableName))
                return new Tuple<string, DbForeignKeyInfo[]>(fkTableName, new DbForeignKeyInfo[] { outerFk[pkTableName] });

            outerFk = this.GetForeignKey(pkTableName).ToDictionary(x => x.Table);
            if (outerFk.ContainsKey(fkTableName))
                return new Tuple<string, DbForeignKeyInfo[]>(pkTableName, new DbForeignKeyInfo[] { outerFk[fkTableName] });
            //关联表
            var allTableName = this.GetAllTables();
            foreach (var table in allTableName)
            {
                this.GetForeignKey(table);
            }
            foreach (var item in fkCache[this.ProviderElement.Key])
            {
                var fkDic = item.Value.ToDictionary(x => x.Table);
                if (fkDic.ContainsKey(pkTableName) && fkDic.ContainsKey(fkTableName))
                    return new Tuple<string, DbForeignKeyInfo[]>(item.Key,
                        new DbForeignKeyInfo[] { fkDic[pkTableName], fkDic[fkTableName] });
            }

            return null;
        }

        private static string GET_ALL_COLUM = "select name from syscolumns WHERE id = OBJECT_ID('{0}')";
        protected override ConcurrentSet<string> GetTableColums(string tableName)
        {
            var sql = string.Format(GET_ALL_COLUM, tableName);
            var colums = new DataReader<string>(new TranslateResult(sql, new List<DbParameter>()), this.ProviderElement, null).ToList();
            ConcurrentSet<string> obj = new ConcurrentSet<string>();
            var fk = this.GetForeignKey(tableName).ToDictionary(x => x.From);
            var pk = this.GetPrimaryKey(tableName);
            foreach (var item in colums)
            {
                if (fk.ContainsKey(item.ToLower()) || item.ToLower() == pk) continue;
                obj.Add(item.ToLower());
            }
            return obj;
        }

        protected override void RemoveFkCache(string type)
        {
            fkCache[ProviderElement.Key][type] = null;
        }

        private class PrimaryKeyTemp
        {
            public string TableName { get; set; }
            public string CloumName { get; set; }
        }

        private class TableTemp
        {
            public string Name { get; set; }
        }
    }
}
